<?php
/*
 * Copyright (c) 2011 Todd Brady
 *
 * PS3Theatre is freely distributable under the terms of the MIT license.
 * For details, see the MIT-Liscence web site: http://www.opensource.org/licenses/mit-license.php 
 */
    include_once('../classes/TVDb.class.php');
	include_once("../includes/db_connect.php");
 	require_once '../classes/StringUtil.class.php';
	
	$typeString = "TV Shows";	
	$tvapi = new TVDb('CE4CE0C779385D53');
	
	$action=$_GET["action"];
	$showId=$_GET["showId"];
	$seriesId=$_GET["seriesId"];
	
	$moveThe = $_SESSION['moveThe'];
	$filesTouched = array();
	$seriesTracker = array();
	$changesFound = false;
	
	if($action == 'scrapeAll'){
		scrapeAllShows();
	}else if($action == 'scrapeShowByDBId'){
		scrapeTVShowByDBId($showId);
	}else if($action == 'duplicateSelected'){
		duplicateSelected($showId, $seriesId);
	}
	
	//
	//
	//
	function scrapeTVShowByDBId($TVShowId){
		global $tvapi;
		
		//query db and get filename
		$tvEpResult=mysql_query("SELECT t.filename,t.series_id,t.season,t.episode FROM tbl_tvshow_episode t WHERE t.id=$TVShowId");
		$row = mysql_fetch_array($tvEpResult);
		
		$fileName = $row['filename'];
		$extStartPos = strrpos($fileName, '.');
		$justFileName = substr($fileName,0,$extStartPos);
		
		//make sure filename is ok
		if(!preg_match("/[sS]{1}[0-9]{2}[eE][0-9]{2}/", $justFileName, $matches, PREG_OFFSET_CAPTURE)){
			//update error
			updateEpRecord($TVShowId, -1, -1, 3, 0, 0, NULL, NULL, NULL);
			echo 'invalid filename';
			return;
		}
		
		$justShowTitle = str_replace($matches[0][0],'',$justFileName);
		$seasonNum = (int)substr($matches[0][0],1,2);
		$episodeNum = (int)substr($matches[0][0],4,2);
		$year = "";
							
		//pull year out of title if necessary
		if(preg_match("/[0-9]{4}/", $justShowTitle, $matches, PREG_OFFSET_CAPTURE)){
			$year = $matches[0][0];
			$justShowTitle = str_replace($matches[0][0],'',$justShowTitle);
		}
			
		$removeChars = array("'",".","/",",","[","]","{","}","(",")","|","-","_");
		$justShowTitle = str_replace($removeChars, " ", $justShowTitle);
		$xml = $tvapi->GetSeriesSearchArray($justShowTitle, $year);
		
		if($xml == ''){
			//series couldnt be found, update error
			updateEpRecord($TVShowId, -1, -1, 4, $seasonNum, $episodeNum, NULL, NULL, NULL);
			echo 'no results';
			return;
					
		}else if(count($xml->Series) > 1){
			//if year in filename then try to narrow it down
			if($year != ''){
				$i = 0;
				$yearMatches = array();
				
				while($i<count($xml->Series)){
					if(strpos($xml->Series[$i]->FirstAired, $year) === 0){
						array_push($yearMatches,$xml->Series[$i]);
					}
					$i++;
				}
			}

			if(count($yearMatches) == 1){
				//1 year match
				$series = $tvapi->GetSerieData($yearMatches[0]->seriesid);

				// get episode id for file
				$epArray = $tvapi->GetEpisodeData($series['id'],$seasonNum,$episodeNum);

				if($epArray != ''){
					//insert series
					$dbSeriesId = insertSeriesRecord($series);
										
					//insert episode info
					updateEpRecord($TVShowId, $epArray['id'], $dbSeriesId, 'null', $seasonNum, $episodeNum, iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['airdate']), iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['name']), iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['description']));
					echo 'updated';
					return;
					
					
				}			
			}else if(count($yearMatches) === 0){
				//create table html and pass it back to javascript method
				echo createDuplicateTvShowTable($TVShowId, $justShowTitle, count($xml->Series), $xml->Series);
				return;

			}else{
				//create table html and pass it back to javascript method
				echo createDuplicateTvShowTable($TVShowId, $justShowTitle, count($yearMatches), $yearMatches);
				return;
			}
			
			
		}else{
			$series = $tvapi->GetSerieData($xml->Series->seriesid);
								 
			// get episode id for file
			$epArray = $tvapi->GetEpisodeData($series['id'],$seasonNum,$episodeNum);

			if($epArray != ''){
				//insert series
				$dbSeriesId = insertSeriesRecord($series);
									
				//insert episode info
				updateEpRecord($TVShowId, $epArray['id'], $dbSeriesId, 'null', $seasonNum, $episodeNum, iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['airdate']), iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['name']), iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['description']));
				echo 'updated';
				return;
			}
		}
	}
	
	//
	//
	//
	function duplicateSelected($showId, $seriesId) {
		global $tvapi;

		//query db and get filename
		$tvEpResult=mysql_query("SELECT t.filename,t.series_id,t.season,t.episode FROM tbl_tvshow_episode t WHERE t.id=$showId");
		$row = mysql_fetch_array($tvEpResult);
		
		$seasonNum = $row['season'];
		$episodeNum = $row['episode'];
		
		$seriesInfo = $tvapi->GetSerieData($seriesId);

		// get episode id for file
		$epArray = $tvapi->GetEpisodeData($seriesId,$seasonNum,$episodeNum);
//    	$tvdbEpisodeId =  $tvapi->GetEpisodeId($seriesId,$seasonNum,$episodeNum);
		
		if($epArray != ''){
			//insert series
			$dbSeriesId = insertSeriesRecord($seriesInfo);
									
			//insert episode info
			//$epArray = $tvapi->GetEpisodeData($tvdbEpisodeId);
			updateEpRecord($showId, $epArray['id'], $dbSeriesId, 'null', $seasonNum, $episodeNum, iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['airdate']), iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['name']), iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['description']));
			echo 'success';
			return;
		}
	}
/*
    // create object
    $tvapi = new Thetvdb('CE4CE0C779385D53');

    // get serie id for 'fringe'
    $serieid = $tvapi->GetSerieId('fringe');

    // get episode id for fringe S01E01
    $episodeid = $tvapi->GetEpisodeId($serieid,1,1);

    // get information about the episode
    $ep_info = $tvapi->GetEpisodeData($episodeid);

    // get information about the serie, without the episodes
    $serie_info = $tvapi->GetSerieData($serieid);

    // get information about the serie, including the episodes
    $serie_info = $tvapi->GetSerieData($serieid,true);
	*/
	
	//
	//
	//
	function scrapeAllShows(){
		set_time_limit(300);
		global $typeString;
		
		$directoriesListQuery = "SELECT d.id, d.computer_path, d.http_path from tbl_directories d, tbl_types t WHERE 		d.type_id=t.id and t.type='$typeString'";
			
		$directories=mysql_query($directoriesListQuery);
		$extArray = getExtensionArray($typeString);
		global $filesTouched;
		global $changesFound;
	
		while ($dirRow = mysql_fetch_array($directories)) {
			$path = $dirRow['computer_path'];
			$dirId = $dirRow['id'];
			$files = scandir($path);

			iterateThroughFiles($path, '', $files, $extArray, $dirId);
		}

		//delete files if needed
		deleteFilesNotFound();
		
		if($changesFound){
			//tell requesting page it worked
			echo 'success';
		}else{
			//tell requesting page no changes
			echo 'no changes';
		}
	}
	
	//
	//
	//
	function getExtensionArray($typeString){
		$extensionsListQuery = sprintf("SELECT f.extension from tbl_fileextensions f, tbl_types t where f.type_id=t.id AND t.type='$typeString' and is_active='%s'",
							   	   mysql_real_escape_string("Y"));
					
		$extensions = mysql_query($extensionsListQuery);
		$extArray = array();

		//make file extension array
		while ($fileExtRow = mysql_fetch_array($extensions)) {
			$extArray[] = $fileExtRow['extension'];
		}
		
		return $extArray;
	}
	
	//
	//
	//
	function iterateThroughFiles($path, $subfolderPath, $files, $extArray, $dirId){
		global $tvapi;
		for( $ctr = 0; $ctr < sizeof( $files ); $ctr++ ) {
			$fileName = $files[$ctr];
			$extStartPos = strrpos($fileName, '.');
			$ext = substr($fileName,$extStartPos+1);
			$justFileName = substr($fileName,0,$extStartPos);
			$justShowTitle;
			$seasonNum = 0;
			$episodeNum = 0;
			$removeChars = array("'",".","/",",","[","]","{","}","(",")","|","-","_");
			$searchString = '';

			if($extStartPos == '' && $fileName != '.'){
				//its a directory...go inside directory and find files
				iterateThroughFiles($path,$subfolderPath.$fileName.'/',scandir($path.$subfolderPath.$fileName.'/'),$extArray,$dirId);
			}else{
				///make sure extension is valid
				if(in_array($ext,$extArray)){
					if(neverBeenScraped($fileName, $dirId, $subfolderPath)){
						//file has never been in database

						//check filename make sure it has all the right parts
						$matches = validFileName($dirId, $subfolderPath, $fileName, $justFileName);
						if($matches){
							$justShowTitle = str_replace($matches[0][0],'',$justFileName);
							$seasonNum = (int)substr($matches[0][0],1,2);
							$episodeNum = (int)substr($matches[0][0],4,2);
							$year = '';
							
							//pull year out of title if necessary
							if(preg_match("/[0-9]{4}/", $justShowTitle, $matches, PREG_OFFSET_CAPTURE)){
								$year = $matches[0][0];
								$justShowTitle = str_replace($matches[0][0],'',$justShowTitle);
							}
							
							$searchString = StringUtil::createCleanTitle($fileName);
							
							// get series id
    						$seriesInfo = searchForSeriesIdByName($searchString, $year, $dirId, $subfolderPath, $fileName, $justFileName, $seasonNum, $episodeNum, $justFileName);
							if($seriesInfo != ''){
//								 $seriesInfo = $tvapi->GetSerieData($tvdbSeriesId);
								 
								// get episode id for file
								//$epArray = $tvapi->parseEpisodeData($seriesInfo, $seasonNum, $episodeNum);
	   							$epArray = getEpisodeId($searchString, $seriesInfo, $dirId, $subfolderPath, $fileName, $dbSeriesId, $seasonNum, $episodeNum, $justFileName);
								if($epArray != ''){
									//insert series
									$dbSeriesId = insertSeriesRecord($seriesInfo);
									
									//insert episode info
									//$epArray = $tvapi->GetEpisodeData($tvdbEpisodeId);
									insertEpisodeRecord($dirId, $subfolderPath, $fileName, $epArray, $dbSeriesId);

								}//ep
							}//series
					   }//valid filename
					}//never scraped
				}//in ext array
			}//if dir
		}//for
	}
	
	//
	//
	//
	function neverBeenScraped($fileName, $dirId, $subfolderPath){
		$fileNameNeverQuery = sprintf("select id from tbl_tvshow_episode where directory_id=%s and subfolder_path='%s' and filename='%s'",
					  		  mysql_real_escape_string($dirId),
   						      mysql_real_escape_string($subfolderPath),
							  mysql_real_escape_string($fileName));
		$fileNameNeverCheck=mysql_query($fileNameNeverQuery);
		$numRows = mysql_num_rows($fileNameNeverCheck);
		global $filesTouched;
		
		if($numRows > 0){
			$row = mysql_fetch_array($fileNameNeverCheck);
			
			//add to files found array
			array_push($filesTouched, $row['id']);
		}
		
		return $numRows == 0;
	}
	
	//
	//
	//
	function searchForSeriesIdByName($searchString, $year, $dirId, $subfolderPath, $fileName, $justFileName, $seasonNum, $episodeNum, $justFileName){
		global $tvapi;
		global $seriesTracker;
		
		if (array_key_exists($searchString, $seriesTracker)) {
			return $seriesTracker[$searchString];
		}else{
			$xml = $tvapi->GetSeriesSearchArray($searchString, $year);

			if($xml == ''){
				//series couldnt be found, insert blank episode record
				insertEmptyRecord($dirId, $subfolderPath, $fileName, -1, $seasonNum, $episodeNum, $justFileName, 4);
				return '';
			}else if(count($xml->Series) > 1){
				//if year in filename then try to narrow it down
				if($year != ''){
					$i = 0;
					$yearMatches = array();
					while($i<count($xml->Series)){
						if(strpos($xml->Series[$i]->FirstAired, $year) === 0){
							array_push($yearMatches,$xml->Series[$i]);
						}
						$i++;
					}
				}
				
				if(count($yearMatches) == 1){
					//1 year match
					$seriesAr = $tvapi->GetSerieData($yearMatches[0]->seriesid, true);
					$seriesTracker[$searchString] = $seriesAr;
					return $seriesAr;
					
				}else{
					//more than 1 series found
					insertEmptyRecord($dirId, $subfolderPath, $fileName, -1, $seasonNum, $episodeNum, $justFileName, 6);
					return '';
				}
			}else{
				$seriesAr = $tvapi->GetSerieData($xml->Series->seriesid, true);
				$seriesTracker[$searchString] = $seriesAr;
				return $seriesAr;
			}
		}
	}
	
	//
	//
	//
	function getEpisodeId($searchString, $seriesInfo, $dirId, $subfolderPath, $fileName, $dbSeriesId, $seasonNum, $episodeNum, $justFileName){
		global $tvapi;
		global $seriesTracker;
//		$epArray = $tvapi->GetEpisodeData($seriesInfo['id'],$seasonNum,$episodeNum);
		
		if (array_key_exists($searchString, $seriesTracker) && array_key_exists('s'.(int)$seasonNum.'e'.(int)$episodeNum,$seriesTracker[$searchString]['episodes'])) {
			return $seriesTracker[$searchString]['episodes']['s'.(int)$seasonNum.'e'.(int)$episodeNum];
		}else{
			//found series, couldnt find episode.
			//Insert series record
			$dbSeriesId = insertSeriesRecord($seriesInfo);
															
			//insert blank ep record with error
			insertEmptyRecord($dirId, $subfolderPath, $fileName, $dbSeriesId, $seasonNum, $episodeNum, $justFileName, 5);
			return '';
									
		}
		//else{
		//	return $epArray;
		//}
	}
	
	//
	//
	//
	function validFileName($dirId, $subfolderPath, $fileName, $justFileName){
		if(preg_match("/[sS]{1}[0-9]{2}[eE][0-9]{2}/", $justFileName, $matches, PREG_OFFSET_CAPTURE)){
			return $matches;
		}else{
			//invalid filename, insert blank episode record
			insertEmptyRecord($dirId, $subfolderPath, $fileName, -1, 0, 0, $justFileName, 3);
		}
	}
	
	//
	//
	//
	function insertSeriesRecord($seriesArray){
		global $changesFound;
		$changesFound = true;
		
		$image = strtolower(str_replace(" ", "", iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['network'])));
		
		//make sure series isnt already in db, if it is return database series id
		$seriesExistsQuery = sprintf("select id from tbl_tvshow_series where tvdbseries_id=%s",
					  		  mysql_real_escape_string($seriesArray['id']));
		$existsCheck=mysql_query($seriesExistsQuery);
		$numRows = mysql_num_rows($existsCheck);
		
		if($numRows == 1){
			$row = mysql_fetch_array($existsCheck);
			return $row['id'];
		}
		
		//it doesnt exist, insert new record
		$insertQuery = sprintf("INSERT INTO tbl_tvshow_series (tvdbseries_id,name,description,content_rating,first_aired,genre,network,network_image,rating,runtime)
VALUES (%s,'%s','%s','%s','%s','%s','%s','%s',%s,%s)", 
							   	   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['id'])),
								   mysql_real_escape_string(updateSeriesTitle(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['name']))),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['description'])),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['contentRating'])),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['firstAired'])),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['genre'])),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['network'])),
								   mysql_real_escape_string($image),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['rating'])),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$seriesArray['runtime'])));

		//insert series record
		mysql_query($insertQuery) or die('error inserting series record: query: '.$insertQuery.'error:'.mysql_error());
					
		//return inserted id
		return mysql_insert_id();
	}
	
	//
	//
	//
	function updateSeriesTitle($seriesTitle){
		global $moveThe;
		
		if($moveThe == 'Y' && substr($seriesTitle,0,4) == 'The '){
			return substr($seriesTitle,4).', The';
		}else{
			return $seriesTitle;
		}
	}
	
	//
	//
	//
	function insertEpisodeRecord($dirId, $subfolderPath, $fileName, $epArray, $dbSeriesId){
		global $filesTouched;	
		global $changesFound;
		$changesFound = true;
		
		$insertQuery = sprintf("INSERT INTO tbl_tvshow_episode (directory_id,subfolder_path,filename,tvdbepisode_id,series_id,season,episode,airdate,name,description)
VALUES (%s, '%s', '%s', %s, %s, %s, %s, '%s', '%s', '%s')", 
							   	   mysql_real_escape_string($dirId),
								   mysql_real_escape_string($subfolderPath),
								   mysql_real_escape_string($fileName),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['id'])),
								   mysql_real_escape_string($dbSeriesId),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['season'])),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['episode'])),
								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['airdate'])),
   								   mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['name'])),
								   str_replace('\n', '', mysql_real_escape_string(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$epArray['description']))));

		//insert episode record
		mysql_query($insertQuery) or die('error inserting episode record: query: '.$insertQuery.'error:'.mysql_error());
		
		//add to files found array
		array_push($filesTouched, mysql_insert_id());
	}
	
	//
	//
	//
	function insertEmptyRecord($dirId, $subfolderPath, $fileName, $seriesId, $seasonId, $episodeId, $justFileName, $errorId) {
		global $filesTouched;
		global $changesFound;
		
		$changesFound = true;
		
		$insertQuery = sprintf("INSERT INTO tbl_tvshow_episode (directory_id,subfolder_path,filename,tvdbepisode_id,series_id,error_id,season,episode,name)
VALUES (%s,'%s','%s',%s,%s,%s,%s,%s,'%s')", 
							   	   mysql_real_escape_string($dirId),
								   mysql_real_escape_string($subfolderPath),
								   mysql_real_escape_string($fileName),
								   mysql_real_escape_string(-1),
								   mysql_real_escape_string($seriesId),
   								   mysql_real_escape_string($errorId),
								   mysql_real_escape_string($seasonId),
								   mysql_real_escape_string($episodeId),
								   mysql_real_escape_string($title));

		//insert movie record
		mysql_query($insertQuery) or die('error inserting record: query: '.$insertQuery.'error:'.mysql_error());
					
		//add to files found array
		array_push($filesTouched, mysql_insert_id());
	}
	
	//
	//
	//
	function updateEpRecord($id, $tvdbEpId, $seriesId, $errorId, $season, $episode, $airdate, $name, $description) {
		$updateQuery = sprintf("UPDATE tbl_tvshow_episode SET tvdbepisode_id=%s,series_id=%s,error_id=%s,season=%s,episode=%s,airdate='%s',name='%s',description='%s' WHERE id=%s", 
							   	   mysql_real_escape_string($tvdbEpId),
								   mysql_real_escape_string($seriesId),
								   mysql_real_escape_string($errorId),
								   mysql_real_escape_string($season),
								   mysql_real_escape_string($episode),
   								   mysql_real_escape_string($airdate),
								   mysql_real_escape_string($name),
								   str_replace('\n', '', mysql_real_escape_string($description)),
								   mysql_real_escape_string($id));
								   
	    //update tvshow record
		mysql_query($updateQuery) or die('error updating record: query: '.$updateQuery.'error:'.mysql_error());
	}
	
	//
	//
	//
	function deleteFilesNotFound(){
		global $filesTouched;
		global $changesFound;

		$deleteString = "DELETE FROM tbl_tvshow_episode WHERE id NOT IN ";
		$idString = "(";
		$i=0;
		
		while($i < count($filesTouched)){
			if($i != 0){
				$idString.=", ";
			}
				
			$idString.=$filesTouched[$i];
			$i++;
		}
		$idString.=");";
		
		$deleteQuery = mysql_query($deleteString.$idString) or die('error deleting tv episodes: '.mysql_error());
		
		//if any rows deleted set changes found
		if(mysql_affected_rows() > 0){
			$changesFound = true;
		}
		
		//delete series records that have been orphaned
		$deleteSeriesString = "DELETE FROM tbl_tvshow_series WHERE id NOT IN (SELECT distinct series_id FROM tbl_tvshow_episode)";
		
		$deleteSeriesQuery = mysql_query($deleteSeriesString) or die('error deleting tv series: '.mysql_error());
		
		//if any rows deleted set changes found
		if(mysql_affected_rows() > 0){
			$changesFound = true;
		}
	}
	
	//
	//
	//
	function createDuplicateTvShowTable($epId, $seriesTitle, $numResults, $seriesArray){
		$tableString = "There are multiple search results for the series title '<b>$movieTitle</b>'.".'  Please select the correct one:<br/><table width="95%" border="0" align="center" cellpadding="0" cellspacing="0" class="setupTable">
		<th></th><th>Title</th><th>Description</th><th>First Aired</th>';
		$i=0;
		
		while($i<$numResults){
			$class = "";
	
			if($i % 2 == 0){
				$class = 'class="alt"';
			}
			
			$tableString.="<tr><td $class><div align='center'>";
			$tableString.='<a href="#" onclick="javascript:selectDup('.$epId.", ".$seriesArray[$i]->seriesid.')">Select</a></div></td>'."<td $class>";
			$tableString.=$seriesArray[$i]->SeriesName."</td><td $class>";
			$tableString.=substr($seriesArray[$i]->Overview,0,75)."...</td><td $class>";
			$tableString.=$seriesArray[$i]->FirstAired.'</td></tr>';	
			
			$i++;
		}
		$tableString.='<tr>
    <td class="footer" colspan="4"></td>
  </tr></table>';
		return $tableString;
	}

?>